package com.cloud.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Jdbc数据库链接工具
 *
 * @author yzj
 */
public class JDBCUtils {

    public Connection conn = null;
    public Statement stat = null;
    public ResultSet result = null;

    /**
     * 创建JDBC链接
     *
     * @param jdbcPath
     * @param jdbcDrive
     * @param jdbcUser
     * @param passWord
     */
    public JDBCUtils(String jdbcPath, String jdbcDrive, String jdbcUser, String passWord) {
        try {
            //1、注册驱动
            Class.forName(jdbcDrive);
            //2、获取连接
            this.conn = DriverManager.getConnection(jdbcPath, jdbcUser, passWord);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * JDBC查询返回转list,Map
     *
     * @param rs
     * @return
     */
    public static List<Map<String, Object>> convertList(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加SQL 并返回主键
     *
     * @param sql
     * @return
     */
    public Long saveSqlBackId(String sql) {
        try {
            stat = conn.createStatement();
            // 执行语句
            stat.execute(sql, Statement.RETURN_GENERATED_KEYS);
            result = stat.getGeneratedKeys();
            // 输出查询结果
            if (result.next()) {
                return result.getLong(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stat.close();
                result.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0L;
    }

    /**
     * 批量添加SQL
     *
     * @param sqls 多条
     * @return
     */
    public boolean saveBatchSql(String sqls) {
        // TODO Auto-generated method stub
        try {
            sqls = sqls.replaceAll("\n", "");
            // 判断最后一位是否是分号
            if (";".equals(sqls.substring(sqls.length() - 1))) {
                sqls = sqls.substring(0, sqls.length() - 1);
            }
            // 获得语句执行者
            stat = conn.createStatement();
            for (String sql : sqls.split(";")) {
                // 执行SQL语句
                stat.execute(sql);
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stat.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 查询SQL 列表
     *
     * @param sql
     * @return
     */
    public List<Map<String, Object>> selectSqlList(String sql) {
        // TODO Auto-generated method stub
        try {
            stat = conn.createStatement();
            // 执行语句
            result = stat.executeQuery(sql);
            // 输出查询结果
            return convertList(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stat.close();
                result.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 关闭链接
     *
     * @throws Exception
     */
    public void closure() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
